
use -- the proper database below according to the current environment
	Events_Temp 
 -- Test_Events_Temp
 -- Live_Events_Temp

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PublishedIncidents ADD
	IsMajorIncidentInChain bit NOT NULL CONSTRAINT DF_PublishedIncidents_IsMajorIncident DEFAULT 0,
	ImpactTrendInChain int NOT NULL CONSTRAINT DF_PublishedIncidents_ImpactTrend DEFAULT 0,
	RiskTrendInChain int NOT NULL CONSTRAINT DF_PublishedIncidents_RiskTrend DEFAULT 0
GO
ALTER TABLE dbo.PublishedIncidents SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

/****** Object:  StoredProcedure [dbo].[spGetIncidentsByCompanyIds]    Script Date: 12/19/2017 5:18:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[spGetIncidentsByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT PublishedIncidents.[Id]
		  ,[CompanyId]
		  ,[EventIndicatorId]
		  ,[EventIndicatorName]
		  ,[IncidentId]
		  ,[Type]
		  ,[Name]
		  ,[Summary]
		  ,[Analysis]
		  ,[MainTag]
		  ,[HasSupplyChainSpecialTag]
		  ,[Source]
		  ,[IncidentDate]
		  ,[LocationAddress]
		  ,[LocationLatitude]
		  ,[LocationLongitude]
		  ,[ImpactScore]
		  ,[RiskScore]
		  ,[LastUpdated]
		  ,[Timestamp]
		  ,[ExternalUrl]
		  ,[IsMajorIncidentInChain]
		  ,[ImpactTrendInChain]
		  ,[RiskTrendInChain]
	FROM [dbo].[PublishedIncidents] AS PublishedIncidents
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = PublishedIncidents.CompanyId
	 
END

GO 


with LightweightPublishedIncidents as (select Id, EventIndicatorId, IncidentId, IncidentDate, ImpactScore, RiskScore from PublishedIncidents), 
	ChainedPublishedIncidents as 
	(select *, 
			IsMajorIncidentInChain = case when ROW_NUMBER() over (partition by EventIndicatorId, IncidentId order by (select MAX(Score) from (values (ImpactScore), (RiskScore)) Scores(Score)) desc, 
																														IncidentDate desc) = 1 then 1 else 0 end, 
			IncidentIndexInChain = ROW_NUMBER() over (partition by EventIndicatorId, IncidentId order by IncidentDate)
		from LightweightPublishedIncidents)
update PublishedIncidents set IsMajorIncidentInChain = crt.IsMajorIncidentInChain, 
								ImpactTrendInChain = SIGN(crt.ImpactScore - ISNULL(prev.ImpactScore, crt.ImpactScore)), 
								RiskTrendInChain = SIGN(crt.RiskScore - ISNULL(prev.RiskScore, crt.RiskScore))	
from PublishedIncidents pi 
join ChainedPublishedIncidents crt on pi.Id = crt.Id
left join ChainedPublishedIncidents prev on crt.EventIndicatorId = prev.EventIndicatorId and crt.IncidentId = prev.IncidentId and crt.IncidentIndexInChain = prev.IncidentIndexInChain + 1

GO

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PublishedIncidents
	DROP CONSTRAINT DF_PublishedIncidents_IsMajorIncident
GO
ALTER TABLE dbo.PublishedIncidents
	DROP CONSTRAINT DF_PublishedIncidents_ImpactTrend
GO
ALTER TABLE dbo.PublishedIncidents
	DROP CONSTRAINT DF_PublishedIncidents_RiskTrend
GO
ALTER TABLE dbo.PublishedIncidents SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

